package com.twilight.SofaStream;

import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.preference.PreferenceManager;
import android.util.Log;
import java.util.Date;


public class RSSDB {

	private static final String FEEDS_TABLE = "feeds";
	private static final String ARTICLES_TABLE = "articles";
	private static final String DATABASE_NAME = "rss.db";
	private static final int DATABASE_VERSION = 6;
	private static final String[] ARTICLE_FIELDS = { "article_id", "feed_id", "title", "body", "url", "pubdate", "listened", 
		"remotefile", "localfile", "downloadstate", "filesize", "downloadprogress" };
	private static final String TAG = "GeneriCast";
	
	private Context mContext;

	
    private static class DatabaseHelper extends SQLiteOpenHelper {

    	private Context ctx;
    	
    	private static final String CREATE_TABLE_FEEDS = "create table feeds (feed_id integer primary key autoincrement, "
    			+ "title text not null, description text not null, url text not null, secure boolean not null);"; //, username text, password text);";

    	private static final String CREATE_TABLE_ARTICLES = "create table articles (article_id integer primary key autoincrement, "
    			+ "feed_id int not null, title text not null, body text not null, url text, remotefile text, localfile text, "
    			+ "pubdate long, listened short not null, downloadstate int not null, filesize long not null, downloadprogress int);";

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
    		ctx = context;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE_FEEDS);
			db.execSQL(CREATE_TABLE_ARTICLES);
			String [] statements = ctx.getResources().getStringArray(R.array.create_sql);
			for (String SQL : statements) {
				db.execSQL(SQL);
			}
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS feeds");
            db.execSQL("DROP TABLE IF EXISTS articles");
            onCreate(db);
        }
    }

    DatabaseHelper mDBHelper;
    
	public RSSDB(Context ctx) {
		mContext = ctx;
		mDBHelper = new DatabaseHelper(ctx);
	}

	public long insertFeed(String title, URL url) {
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("title", title);
		String Url = "";
		if (url != null) {
			Url = url.toString();
		}
		values.put("url", Url);
		long res = db.insert(FEEDS_TABLE, null, values);
		db.close();
		return res;
	}

	public boolean deleteFeed(RSSFeed feed) {
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		boolean x = (db.delete(FEEDS_TABLE, "feed_id=" + feed.id.toString(), null) > 0);
		db.close();
		return x;
	}

	public long insertArticle(long feedId, RSSItem article) {
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("feed_id", feedId);
		values.put("title", article.title);
		values.put("body", article.text);
		if (article.url != null)
			values.put("url", article.url.toString());
		if (article.remotefile != null)
			values.put("remotefile", article.remotefile.toString());
		if (article.pubdate != null)
			values.put("pubdate", article.pubdate.getTime());
		values.put("listened", article.listened ? 1 : 0);
		values.put("downloadstate", article.downloadstate.ordinal());
		values.put("filesize", article.filesize);
		values.put("downloadprogress", article.downloadprogress);
		long res = db.insert(ARTICLES_TABLE, null, values);
		db.close();
		return res;
	}

	public boolean deleteArticles(RSSFeed feed) {
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		return (db.delete(ARTICLES_TABLE, "feed_id=" + feed.id.toString(), null) > 0);
	}

	public ArrayList<RSSFeed> getFeeds() {
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		ArrayList<RSSFeed> feeds = new ArrayList<RSSFeed>();
		try {
			Cursor c = db.query(FEEDS_TABLE, new String[] { "feed_id", "title", "description",
					"url", "secure" }, null, null, null, null, null);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				RSSFeed feed = new RSSFeed();
				feed.id = c.getLong(0);
				feed.title = c.getString(1);
				feed.description = c.getString(2);
				try {
					feed.url = new URL(c.getString(3));
				} catch (MalformedURLException e) {
					Log.e(TAG, e.toString());
				}
				feed.secure = c.getShort(4) != 0;
					
				feeds.add(feed);
				c.moveToNext();
			}
			c.close();
		} catch (SQLException e) {
			Log.e(TAG, e.toString());
		}
		db.close();
		return feeds;
	}

	public RSSFeed getFeed(Long id) {
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		RSSFeed feed = null;
		try {
			Cursor c = db.query(FEEDS_TABLE, new String[] { "feed_id", "title", "description","url", "secure" }, 
				"feed_id=" + id.toString(), null, null, null, null);
			feed = new RSSFeed();
			c.moveToFirst();
			feed.id = c.getLong(0);
			feed.title = c.getString(1);
			feed.description = c.getString(2);
			try {
				feed.url = new URL(c.getString(3));
			} catch (MalformedURLException e) {
				Log.e(TAG, e.toString());
			}
			feed.secure = c.getShort(4) != 0;
			c.close();
		} catch (SQLException e) {
			Log.e(TAG, e.toString());
		}
		db.close();
		return feed;
	}

	private RSSItem populateArticle(Cursor c) {
		RSSItem article = new RSSItem();
		article.id = c.getLong(0);
		article.feedId = c.getLong(1);
		article.title = c.getString(2);
		article.text = c.getString(3);
		String url = c.getString(4);
		if (url != null)
			try {
				article.url = new URL(url);
			} catch (MalformedURLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		Long date = c.getLong(5);
		if (date != null)
			article.pubdate = new Date(date);
		article.listened = c.getShort(6) != 0;
		String remotefile = c.getString(7);
		if (remotefile != null)
			try {
				article.remotefile = new URL(remotefile);
			} catch (MalformedURLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		String localfile = c.getString(8);
		if (localfile != null)
		article.localfile = localfile;
		article.downloadstate = RSSItem.downloadstates.values()[c.getInt(9)];
		article.filesize = c.getInt(10);
		article.downloadprogress = c.getInt(11);
		return article;
	}
	
	public long[] getArticles(ArrayList<RSSFeed> feeds, boolean showNew) {
		long[] article_ids = null;
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		String query = "feed_id IN (";
		Iterator<RSSFeed>iter = feeds.iterator();
		while (iter.hasNext()) {
			RSSFeed feed = iter.next();
			query += String.format("%d", feed.id);
			if (iter.hasNext()) query += ", ";
		}
		query += ")";
		if (showNew) {
			query += String.format(" AND (listened = 0 OR downloadstate <> %d)", RSSItem.downloadstates.UNDOWNLOADED.ordinal());
		}
		try {
			String[] id_field = {"article_id"};
			Cursor c = db.query(ARTICLES_TABLE, id_field, 
					query, null, null, null, "pubdate DESC", null);

			int numRows = c.getCount();
			article_ids = new long[numRows];
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				article_ids[i] = c.getLong(0);
				c.moveToNext();
			}
			c.close();
		} catch (SQLException e) {
			Log.e(TAG, e.toString());
		}
		db.close();
		return article_ids;
	}
	
	public void getArticles(RSSFeed feed) {
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		try {
			Cursor c = db.query(ARTICLES_TABLE, ARTICLE_FIELDS,
					"feed_id=" + feed.id.toString(), null, null, null, null);

			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				RSSItem article = populateArticle(c);
				feed.articles.add(article);
				c.moveToNext();
			}
			c.close();
		} catch (SQLException e) {
			Log.e(TAG, e.toString());
		}
		db.close();
	}
	
	public RSSItem getArticle(Long articleId) {
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		RSSItem result = null;
		try {
			Cursor c = db.query(ARTICLES_TABLE, ARTICLE_FIELDS,
					"article_id=" + articleId.toString(), null, null, null, null);
			if (c.getCount() > 0) {
				c.moveToFirst();
				result = populateArticle(c);
			}
			c.close();
		} catch (SQLException e) {
			Log.e(TAG, e.toString());
		}
		db.close();
		return result;
	}

	public void updateArticle(Long articleId, ContentValues values) {
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		db.update(ARTICLES_TABLE, values, "article_id=" + articleId.toString(), null);
		db.close();
	}

	public void clearDatabase() {
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		mDBHelper.onUpgrade(db, DATABASE_VERSION-1, DATABASE_VERSION);
		db.close();
	}

	public ArrayList<RSSFeed> getActiveFeeds() {
    	ArrayList<RSSFeed> feeds = getFeeds();

        //Filter out any feeds not selected in the preferences
        SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(mContext);
        int i = 0;
        while (i < feeds.size()) {
        	RSSFeed feed = feeds.get(i);
        	String prefId = String.format("feed_%d", feed.id);
        	if (prefs.getBoolean(prefId, false)) {
        		i++;
        	} else {
        		feeds.remove(i);
        	}
        }
        return feeds;
	}

	public RSSItem findExistingArticle(RSSItem article) {
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		RSSItem result = null;
		String[] args = {article.url.toString()};
		try {
			Cursor c = db.query(ARTICLES_TABLE, ARTICLE_FIELDS,
					"url=? OR title=? OR remotefile = ?", args, null, null, null);
			if (c.getCount() > 0) {
				c.moveToFirst();
				result = populateArticle(c);
			}
			c.close();
		} catch (SQLException e) {
			Log.e(TAG, e.toString());
		}
		db.close();
		return result;
	}
}


